-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tidus
-- Create date: 28/8/2011
-- Description:	Contract
-- =============================================
USE QLNS
Go
IF OBJECT_ID ('sp_ListContract', 'p') IS NOT NULL
	DROP PROC sp_ListContract
Go
CREATE PROCEDURE sp_ListContract
	@Record int,
	@Page int,
	@Sort varchar(50),
	@flag bit
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		DECLARE @Sql varchar(1000);
		IF @Page = 0
			SET @Sql = 'SELECT Contract_Id, Contract_Name FROM Contract ORDER BY '+@Sort+' ASC' 
		ELSE
			BEGIN
				DECLARE @Min varchar(3);
				DECLARE @Max varchar(3);
				SET @Min = CAST((@Record*(@Page-1)) AS varchar(3));
				SET @Max = CAST((@Record*@Page+1) AS varchar(3));
				IF @flag = 'true' -- true la theo chieu be -> lon 
					SET @Sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@Sort+' ASC) AS rownum, 
									* FROM Contract) AS a WHERE rownum >'+@Min+' AND rownum < '+@Max+'';
				ELSE -- false la tu lon -> be
					SET @Sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@Sort+' DESC) AS rownum, 
									* FROM Contract) AS a WHERE rownum >'+@Min+' AND rownum < '+@Max+'';
			END
		EXEC (@Sql)
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*
EXEC sp_ListContract 1, 1, 'Contract_Id', true
*/

/*********************** INSERT Contract ************************/

/******************** [2]INSERT ********************/
IF OBJECT_ID ('sp_InsertContract', 'p') IS NOT NULL
	DROP PROC sp_InsertContract
Go
CREATE PROCEDURE sp_InsertContract

	@Contract_Name nvarchar(255),
	@Contract_Des nvarchar(500),
	@UserInsert int
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		--[0]--
		DECLARE @DateInsert datetime;
		SET @DateInsert = GETDATE();
		--[1]--
		INSERT INTO dbo.Contract
			   ([Contract_Name], [Contract_Des] ,[UserInsert], [DateInsert])
		 VALUES(
				@Contract_Name, @Contract_Des, @UserInsert, @DateInsert);
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*********************** END INSERT Contract ************************/

/*********************** UPDATE Contract ************************/

/******************** [2]UPDATE ********************/
IF OBJECT_ID ('sp_UpdateContract', 'p') IS NOT NULL
	DROP PROC sp_UpdateContract
Go
CREATE PROCEDURE sp_UpdateContract
	@Contract_Id int,
	@Contract_Name nvarchar(255),
	@Contract_Des nvarchar(500),
	@UserUpdate int
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		--[0]--
		DECLARE @DateUpdate datetime;
		SET @DateUpdate = GETDATE();
		--[1]--
		UPDATE dbo.Contract
			SET [Contract_Name] = @Contract_Name
			  ,[Contract_Des] = @Contract_Des
			  ,[UserUpdate] = @UserUpdate
			WHERE Contract_Id = @Contract_Id
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*********************** END UPDATE Contract ************************/